package cn.huhst.cas.weisiqian.dao;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import cn.huhst.cas.weisiqian.dbhelp.DBHelper;
import cn.huhst.cas.weisiqian.pojo.ThreeTuple;
import cn.huhst.cas.weisiqian.pojo.User;

public class UserDao {

	private Logger logger = Logger.getLogger(UserDao.class);

	public User searchByNameAndPassword(String username, String userpassword) {
		User user = null;
		Connection connection = DBHelper.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM users WHERE name=? AND password=?";
		logger.info("sql:" + sql);
		try {
			pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, userpassword);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				user = new User();
				user.setRole(rs.getInt("role"));
				user.setUsername(rs.getString("name"));
				user.setUserpassword(rs.getString("password"));
				logger.info("查询到用户:" + user);
			}
		} catch (SQLException e) {
			logger.error("查询用户名为" + username + "密码为" + userpassword + "的用户信息出错", e);
		} finally {
			DBHelper.closeDBHelp(connection, pstmt, rs);
			logger.info("关闭数据库连接");
		}
		return user;
	}

	public int addUser(User user) throws DuplicateEntryException {
		Connection connection = DBHelper.getConnection();
		PreparedStatement pstmt = null;
		String sql = "INSERT INTO users value(?,?,?)";
		int count = 0;
		logger.info("sql:" + sql);
		try {
			pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, user.getUsername());
			pstmt.setString(2, user.getUserpassword());
			pstmt.setInt(3, user.getRole());
			count = pstmt.executeUpdate();
		} catch (com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException e) {
			logger.error("用户记录已存在", e);
			throw new DuplicateEntryException("用户记录已存在");
		} catch (SQLException e) {
			logger.error("添加：" + user + " 失败", e);
		} finally {
			DBHelper.closeDBHelp(connection, pstmt, null);
			logger.info("关闭数据库连接");
		}
		return count;
	}

	public ArrayList<User> queryAll() {
		ArrayList<User> allUsers = new ArrayList<User>();
		Connection connection = DBHelper.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM users";
		logger.info("sql:" + sql);
		try {
			pstmt = connection.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setUsername(rs.getString("name"));
				user.setUserpassword(rs.getString("password"));
				user.setRole(rs.getInt("role"));
				allUsers.add(user);
				logger.debug("user:" + user);
			}
		} catch (SQLException e) {
			logger.error("查询所有用户信息时出错", e);
		} finally {
			DBHelper.closeDBHelp(connection, pstmt, rs);
			logger.info("关闭数据库连接");
		}
		return allUsers;
	}

	/**
	 * 分页查询
	 * 
	 * @param index
	 *            记录下标
	 * @param pageSize
	 *            分页大小
	 * @return
	 */
	public ArrayList<User> limit(int index, int pageSize) {
		ArrayList<User> Users = new ArrayList<User>();
		Connection connection = DBHelper.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM users LIMIT ?,?";
		logger.info("sql:" + sql);
		try {
			pstmt = connection.prepareStatement(sql);
			pstmt.setInt(1, index);
			pstmt.setInt(2, pageSize);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setUsername(rs.getString("name"));
				user.setUserpassword(rs.getString("password"));
				user.setRole(rs.getInt("role"));
				Users.add(user);
				logger.debug("user:" + user);
			}
		} catch (SQLException e) {
			logger.error("分页查询出错", e);
		} finally {
			DBHelper.closeDBHelp(connection, pstmt, rs);
			logger.info("关闭数据库连接");
		}
		return Users;
	}

	public int getCount() {
		int count = 0;
		Connection connection = DBHelper.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT COUNT(*) FROM users";
		logger.info("sql:" + sql);
		try {
			pstmt = connection.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
				logger.info("记录数目为：" + count);
			}
		} catch (SQLException e) {
			logger.error("查询记录数目时出错", e);
		} finally {
			DBHelper.closeDBHelp(connection, pstmt, rs);
			logger.info("关闭数据库连接");
		}
		return count;
	}

	public boolean delete(ThreeTuple<String, String, String>... conditions) {
		boolean result = false;
		Connection connection = DBHelper.getConnection();
		PreparedStatement pstmt = null;
		String sql = "DELETE FROM users WHERE " + conditions[0].getKey() + "=? ";
		logger.info("sql:" + sql);
		int size = conditions.length;
		for (int i = 1; i < size; i++) {
			sql += "AND " + conditions[i].getKey() + "=? ";
		}
		try {
			pstmt = connection.prepareStatement(sql);
			Class<? extends Object> clazz = pstmt.getClass();
			for (int i = 0; i < size; i++) {
				String type = conditions[i].getType();
				type = type.substring(0, 1).toUpperCase() + type.substring(1, type.length());
				String setMethodName = "set" + type;
				Method setMethod = null;
				try {
					setMethod = clazz.getMethod(setMethodName, int.class, String.class);
				} catch (NoSuchMethodException e) {
					logger.error("PreparedStatement中没有" + setMethodName + "方法" + e);
				} catch (SecurityException e) {
					logger.error(e);
				}
				try {
					setMethod.invoke(pstmt, i, conditions[i].getValue());
				} catch (IllegalAccessException e) {
					logger.error(e);
				} catch (IllegalArgumentException e) {
					logger.error(e);
				} catch (InvocationTargetException e) {
					logger.error(e);
				}
			}
			int count = pstmt.executeUpdate();
			if (count > 0) {
				result = true;
				logger.info("删除符合：" + conditions + "条件的用户成功");
			}
		} catch (SQLException e) {
			logger.info("删除符合：" + conditions + "条件的用户失败");
		} finally {
			DBHelper.closeDBHelp(connection, pstmt, null);
			logger.info("关闭数据库连接");
		}
		return result;
	}

	public boolean deleteByPK() {
		return false;
	}

	public boolean deleteUserByUsername(String username) {
		boolean result = false;
		logger.info("准备删除用户：" + username);
		Connection connection = DBHelper.getConnection();
		PreparedStatement pstmt = null;
		String sql = "DELETE FROM users WHERE name=?";
		try {
			pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, username);
			int count = pstmt.executeUpdate();
			if (count > 0) {
				result = true;
				logger.info("删除用户" + username + "成功");
			}
		} catch (SQLException e) {
			logger.error("删除用户：" + username + "失败");
		} finally {
			DBHelper.closeDBHelp(connection, pstmt, null);
			logger.info("关闭数据库连接");
		}
		return result;
	}

	public boolean updateUser(User user) {
		logger.info("修改用户密码：" + user);
		Connection connection = DBHelper.getConnection();
		PreparedStatement pstmt = null;
		int count = 0;
		String sql = "UPDATE users SET password=? WHERE name=?";
		logger.info("sql:" + sql);
		try {
			pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, user.getUserpassword());
			pstmt.setString(2, user.getUsername());
			count = pstmt.executeUpdate();
		} catch (SQLException e) {
			logger.error("修改用户密码出错", e);
		} finally {
			DBHelper.closeDBHelp(connection, pstmt, null);
			logger.info("关闭数据库连接");
		}
		if (count > 0) {
			logger.info("修改用户密码成功");
			return true;
		} else {
			logger.info("修改用户密码失败");
			return false;
		}
	}
}
